﻿using Dapper;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using VOL.Core.DBManager;
using VOL.Core.Enums;
using VOL.Core.Extensions;
using VOL.Core.Filters;
using VOL.Demo.IRepositories;
using VOL.Entity.DomainModels;

namespace VOL.WebApi.Controllers.DbTest
{
    [JWTAuthorize, ApiController]
    [Route("api/dbTest")]
    public class DbTestController : Controller
    {
        private IDemo_GoodsRepository _goodsRepository { get; set; }

        private IDemo_OrderRepository _demo_OrderRepository { get; set; }
        public DbTestController(
            IDemo_GoodsRepository goodsRepository,
            IDemo_OrderRepository demo_OrderRepository
            )
        {
            _demo_OrderRepository = demo_OrderRepository;
            _goodsRepository = goodsRepository;
        }
        /// <summary>
        /// EF访问数据库
        /// </summary>
        /// <returns></returns>
        [Route("ef"), HttpGet]
        public async Task<IActionResult> EF(string goodsName)
        {
            List<Demo_Goods> goods1 = _goodsRepository.FindAsIQueryable(x => x.Enable == 1 && x.GoodsName == goodsName).ToList();

            List<Demo_Goods> goods2 = await _goodsRepository.FindAsIQueryable(x => x.Enable == 1 && x.GoodsName == goodsName).ToListAsync();

            //单表更新
            if (goods1.Count > 0)
            {
                //goods1[0].GoodsName = "测试更新商品";
                //_goodsRepository.Update(goods1[0], x => new { x.GoodsName }, true);
            }
            //批量更新
            _goodsRepository.UpdateRange(goods1, x => new { x.GoodsName, x.GoodsCode }, true);

            var goods = new Demo_Goods()
            {
                GoodsName = "测试",
                GoodsCode = "001"
            };
            //设置创建人信息
            goods.SetCreateDefaultVal();
            //新建操作
            _goodsRepository.Add(goods, true);

            //删除操作
            if (goods1.Count > 0)
            {
                _goodsRepository.DeleteWithKeys(new object[] { goods1[0].GoodsId });
            }

            //按条件查询：WhereNotEmpty,如果goodsName不为空就参与查询条件
            List<Demo_Goods> goods3 = await _goodsRepository
               .FindAsIQueryable(x => x.Enable == 1)
               .WhereNotEmpty(x => x.GoodsName, goodsName, LinqExpressionType.Contains).ToListAsync();

            //多表关联
            List<Permissions> _permissions = (from a in _goodsRepository.DbContext.Set<Sys_Menu>()
                                              join b in _goodsRepository.DbContext.Set<Sys_RoleAuth>()
                                              on a.Menu_Id equals b.Menu_Id
                                              where b.Role_Id == 1 && b.AuthValue != ""
                                              orderby a.ParentId
                                              select new Permissions
                                              {
                                                  Menu_Id = a.Menu_Id,
                                                  ParentId = a.ParentId,
                                                  TableName = a.TableName,
                                                  MenuAuth = a.Auth,
                                                  UserAuth = b.AuthValue
                                              }).ToList();
            //主从表关联
            List<Demo_Order> orderList = _demo_OrderRepository.FindAsIQueryable(x => true)
                  .Include(x => x.Demo_OrderList)
                  .ToList();

            //分页查询
            var data = _demo_OrderRepository.FindAsIQueryable(x => true)
                 .TakePage(1, 30)//TakePage框架封装的分页查询
                 .Select(s => new
                 {
                     s.Customer,
                     s.PhoneNo
                 });

            await Task.CompletedTask;
            return Content("EF");
        }

        /// <summary>
        /// dapper访问数据库
        /// </summary>
        /// <returns></returns>
        [Route("dapper"), HttpGet]
        public async Task<IActionResult> Dapper(string goodsName)
        {
            string sql = "select * from Demo_Goods where GoodsName=@goodsName";

            //单表查询 
            List<Demo_Goods> goods1 = DBServerProvider.SqlDapper.QueryList<Demo_Goods>(sql, new { goodsName });

            //单表异步查询 
            IEnumerable<Demo_Goods> goods2 = await DBServerProvider.SqlDapper.QueryListAsync<Demo_Goods>(sql, new { goodsName });


            sql = @"select * from Demo_Goods where GoodsName=@goodsName; 
                   select * from Demo_Order ";

            //多表查询 
            (List<Demo_Goods>, List<Demo_Order>) data = DBServerProvider.SqlDapper.QueryMultiple<Demo_Goods, Demo_Order>(sql, new { goodsName });

            //多表查询动态类型
            (List<dynamic>, List<dynamic>) data_dy = DBServerProvider.SqlDapper.QueryDynamicMultiple2(sql, new { goodsName });
            //data.Item2

            //多表关联
            sql = @"SELECT order1.OrderNo,order1.OrderType,list.GoodsName
                 FROM Demo_Order AS order1
                INNER JOIN Demo_OrderList AS list
                ON order1.Order_Id=list.Order_Id";
            //多表关联
            List<object> date2 = DBServerProvider.SqlDapper.QueryList<object>(sql, new { goodsName });
            //多表关联返回动态类型
            var date3 = DBServerProvider.SqlDapper.QueryDynamicList(sql, new { goodsName });
            // 遍历多表关联返回动态类型
            foreach (var item in date3)
            {
                Console.WriteLine(item.OrderNo);
            }

            //in查询
            sql = "SELECT * FROM Demo_Goods WHERE Enable IN @values";
            List<Demo_Goods> date4 = DBServerProvider.SqlDapper.QueryList<Demo_Goods>(sql, new { values = new int[] { 0, 1, 2, 3 } });

            //使用参数，可以设置返回值或者指定类型
            //DynamicParameters parameters = new DynamicParameters();
            //parameters.Add("goodsName", goodsName);
            //调用存储过程
            List<dynamic> date6 = _goodsRepository.DapperContext.QueryDynamicList("sp_Test", new { goodsName }, commandType: CommandType.StoredProcedure);

            //生成批量写入的对象
            List<Demo_Goods> list2 = new List<Demo_Goods>() { };
            for (int i = 0; i < 10; i++)
            {
                list2.Add(new Demo_Goods()
                {
                    GoodsId = Guid.NewGuid(),
                    GoodsName = "测试" + i,
                    GoodsCode = "001"
                }); ;
            }
            //SqlBulkCopy批量写入
            _goodsRepository.DapperContext.BulkInsert<Demo_Goods>(list2);

            await Task.CompletedTask;
            return Content("Dapper");
        }
    }
}
